﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;

namespace AspController
{
    public class DataClass
    {
        public DataClass()
        {

        }
    }
    #region GroupDAL Layer
   
    public class Group
    {
        public int GroupID { get; set; }
        public string GroupTitle { get; set; }
        public String GroupSubTitle { get; set; }
        public String GroupImage { get; set; }
        public String GroupDsc { get; set; }        
    }
    public class GroupDAL
    {
        SqlConnection con;
        SqlDataAdapter adp;
        DataSet ds;
        public GroupDAL()
        {
            con = new SqlConnection();
            con.ConnectionString = ConfigurationManager.ConnectionStrings["cn"].ConnectionString;
            //con.Open();

        }

        public List<Group> GetGroup()
        {
            List<Group> groupList = new List<Group>();
            adp = new SqlDataAdapter("Select * from tbGroup", con);
            ds = new DataSet();
            adp.MissingSchemaAction = MissingSchemaAction.AddWithKey;
            adp.Fill(ds, "EmployeeInfo");
            foreach (DataRow dr in ds.Tables["EmployeeInfo"].Rows)
            {
                groupList.Add(new Group()
                {
                    GroupID = Convert.ToInt32(dr["GroupID"]),
                    GroupTitle = dr["GroupTitle"].ToString(),
                    GroupSubTitle = dr["GroupSubTitle"].ToString(),
                    GroupImage = dr["GroupImage"].ToString(),
                    GroupDsc = dr["GroupDsc"].ToString()

                });
            }
            return groupList;
        }

        public void CreateGroup(Group group)
        {
            con.Open();
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = con;
            cmd.CommandText = "Insert into EmployeeInfo Values(@GroupTitle,@GroupSubTitle,@GroupImage,@GroupDsc)";
            cmd.Parameters.AddWithValue("@GroupTitle", group.GroupTitle);
            cmd.Parameters.AddWithValue("@GroupSubTitle", group.GroupSubTitle);
            cmd.Parameters.AddWithValue("@GroupImage", group.GroupImage);
            cmd.Parameters.AddWithValue("@GroupDsc", group.GroupDsc);
            cmd.ExecuteNonQuery();
            con.Close();
        }

        public void UpdateGroup(Group group)
        {
            con.Open();
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = con;
            cmd.CommandText = "Update EmployeeInfo set GroupTitle=@GroupTitle,GroupSubTitle=@GroupSubTitle,GroupImage=@GroupImage,GroupDsc where GroupID=@GroupID";
            cmd.Parameters.AddWithValue("@GroupTitle", group.GroupTitle);
            cmd.Parameters.AddWithValue("@GroupSubTitle", group.GroupSubTitle);
            cmd.Parameters.AddWithValue("@GroupImage", group.GroupImage);
            cmd.Parameters.AddWithValue("@GroupDsc", group.GroupDsc);
            cmd.ExecuteNonQuery();
            con.Close();
        }

        public void DeleteGroup(int EmpNo)
        {
            con.Open();
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = con;
            cmd.CommandText = "Delete Group where GroupID=@GroupID";
            cmd.Parameters.AddWithValue("@GroupID", EmpNo);
            cmd.ExecuteNonQuery();
            con.Close();
        }
    }
    #endregion

    #region ItemDalLayer

    public class Item
    {
        public int ItemID { get; set; }
        public String ItemTitle { get; set; }
        public String ItemSubTitle { get; set; }
        public String ItemDsc { get; set; }
        public String ItemContent { get; set; }
        public String ItemImage { get; set; }
        public int ItemGroupID { get; set; }

    }
    public class ItemDAL
    {
        SqlConnection con;
        SqlDataAdapter adp;
        DataSet ds;
        public ItemDAL()
        {
            con = new SqlConnection();
            con.ConnectionString = ConfigurationManager.ConnectionStrings["cn"].ConnectionString;
            //con.Open();

        }

        public List<Item> GetItem()
        {
            List<Item> itemList = new List<Item>();
            adp = new SqlDataAdapter("Select * from tbItemDetail", con);
            ds = new DataSet();
            adp.MissingSchemaAction = MissingSchemaAction.AddWithKey;
            adp.Fill(ds, "tbItemDetail");
            foreach (DataRow dr in ds.Tables["tbItemDetail"].Rows)
            {
                itemList.Add(new Item()
                {
                    ItemID = Convert.ToInt32(dr["ItemID"]),
                    ItemTitle = dr["ItemTitle"].ToString(),
                    ItemSubTitle = dr["ItemSubTitle"].ToString(),
                    ItemDsc = dr["ItemDsc"].ToString(),
                    ItemContent = dr["ItemContent"].ToString(),
                    ItemImage = dr["ItemImage"].ToString(),
                    ItemGroupID =Convert.ToInt32 (dr["GroupID"])

                });
            }
            return itemList;
        }

        public void CreateItem(Item item)
        {
            con.Open();
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = con;
            cmd.CommandText = "Insert into tbItemDetail Values(@ItemTitle,@ItemSubTitle,@ItemDsc,@ItemContent,@ItemImage,@ItemGroupID)";
            cmd.Parameters.AddWithValue("@ItemTitle", item.ItemTitle);
            cmd.Parameters.AddWithValue("@ItemSubTitle", item.ItemSubTitle);
            cmd.Parameters.AddWithValue("@ItemDsc", item.ItemDsc);
            cmd.Parameters.AddWithValue("@ItemContent", item.ItemContent);
            cmd.Parameters.AddWithValue("@ItemImage", item.ItemImage);
            cmd.Parameters.AddWithValue("@ItemGroupID", item.ItemGroupID);
            cmd.ExecuteNonQuery();
            con.Close();
        }

        public void UpdateItem(Item item)
        {
            con.Open();
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = con;
            cmd.CommandText = "Update EmployeeInfo set ItemTitle=@ItemTitle,ItemSubTitle=@ItemSubTitle,ItemDsc=@ItemDsc, ItemContent=@ItemContent, ItemImage=@ItemImage,ItemGroupId=@ItemGroupID where ItemID=@ItemID";
            cmd.Parameters.AddWithValue("@ItemID", item.ItemTitle);
            cmd.Parameters.AddWithValue("@ItemTitle", item.ItemTitle);
            cmd.Parameters.AddWithValue("@ItemSubTitle", item.ItemSubTitle);
            cmd.Parameters.AddWithValue("@ItemDsc", item.ItemDsc);
            cmd.Parameters.AddWithValue("@ItemContent", item.ItemContent);
            cmd.Parameters.AddWithValue("@ItemImage", item.ItemImage);
            cmd.Parameters.AddWithValue("@ItemGroupID", item.ItemGroupID);
            cmd.ExecuteNonQuery();
            con.Close();
        }

        public void DeleteItem(int itemID)
        {
            con.Open();
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = con;
            cmd.CommandText = "Delete tbItemDetail where ItemID=@ItemID";
            cmd.Parameters.AddWithValue("@GroupID", itemID);
            cmd.ExecuteNonQuery();
            con.Close();
        }
    }
#endregion
}